﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public DataSet QueryAPFOLGE(string client, string krs, string lfd)
        {
            DataSet setReturn = new DataSet();
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1 = OracleDB.CreateCommand();

            or1.CommandText = "SELECT P.*,KSZLIEFNR,KSZLO,KSZLB FROM APFOLGE P LEFT JOIN KSTAGZUO k "
                + " ON P.APFKSTKRS=KSZKSTKRS AND APFKST=KSZKST AND APFAGANG=KSZAGANG "
            + "  WHERE APFMANDANT=:APFMANDANT AND APFANRKRS=:APFANRKRS AND APFALFDNR=:APFALFDNR";
            or1.Parameters.Add("APFMANDANT", OracleType.NVarChar).Value = client;
            or1.Parameters.Add("APFANRKRS", OracleType.NVarChar).Value = krs;
            or1.Parameters.Add("APFALFDNR", OracleType.NVarChar).Value = lfd;
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                tbLocal.TableName = "APFOLGE";
            }
            setReturn.Merge(Result);
            return setReturn;
        }
        public DataSet QueryAPFOLGE(DataRow dwData, int option = 1)
        {
            DataSet setReturn = new DataSet();
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM APFOLGE WHERE  ROWNUM<2 ";
                    break;
                case 1:
                    or1.CommandText = "SELECT P.* FROM APFOLGE P "
                        + "  WHERE APFMANDANT=:APFMANDANT AND APFANRKRS=:APFANRKRS AND APFALFDNR=:APFALFDNR";
                    or1.Parameters.Add("APFMANDANT", OracleType.NVarChar).Value = dwData["APFMANDANT"];
                    or1.Parameters.Add("APFANRKRS", OracleType.NVarChar).Value = dwData["APFANRKRS"];
                    or1.Parameters.Add("APFALFDNR", OracleType.NVarChar).Value = dwData["APFALFDNR"];
                    break;
                case 2:
                    or1.CommandText = "SELECT APFMANDANT,APFANRKRS,APFALFDNR,APFAFOLGE,APFAGANG,APFKSTKRS,APFKST,APFMTYP"
                    + ",KSZLIEFNR,KSZLO,KSZLB FROM APFOLGE P LEFT JOIN KSTAGZUO k "
                    + " ON P.APFKSTKRS=KSZKSTKRS AND APFKST=KSZKST AND APFAGANG=KSZAGANG "
                    + "  WHERE APFMANDANT=:APFMANDANT AND APFANRKRS=:APFANRKRS AND APFALFDNR=:APFALFDNR";
                    or1.Parameters.Add("APFMANDANT", OracleType.NVarChar).Value = dwData["APFMANDANT"];
                    or1.Parameters.Add("APFANRKRS", OracleType.NVarChar).Value = dwData["APFANRKRS"];
                    or1.Parameters.Add("APFALFDNR", OracleType.NVarChar).Value = dwData["APFALFDNR"];
                    break;
                case 3:
                    or1.CommandText = "SELECT APFMANDANT,APFANRKRS,APFALFDNR,APFAFOLGE,APFAGANG,APFKSTKRS,APFKST,APFMTYP"
                    + ",KSZLIEFNR,KSZLO,KSZLB,KSZLP FROM APFOLGE P LEFT JOIN KSTAGZUO k "
                    + " ON P.APFKSTKRS=KSZKSTKRS AND APFKST=KSZKST AND APFAGANG=KSZAGANG "
                    + "  WHERE APFMANDANT=:APFMANDANT ";
                    or1.Parameters.Add("APFMANDANT", OracleType.NVarChar).Value = dwData["APFMANDANT"];
                    break;
                case 4:
                    or1.CommandText = "SELECT DISTINCT APFMANDANT,APFANRKRS,APFALFDNR,APFAFOLGE,APFAGANG,APFKSTKRS,APFKST,APFMTYP,"
                    + "APFPARALKZ,APFBASMGE,APFME,APFRUECKKZ,APFKENNUNG,APFZUSATZ1,APFZUSATZ2,APFZUSATZ3,APFZUSATZ4,APFZUSATZ5,"
                    + "APFZUSTAND,APFFERTIG,APFMATABR,APFDOMPART,APFPRUEFANW,APFPROBE,APFANRKRS,APFALFDNR,APFAFOLGE,APFAEVOR,APFEINSTKA"
                    + ",KSZLIEFNR,KSZLO,KSZLB,KSZLP,KSZMANUELKZ,KSZLAGPLKZ,KSZAUFBKZ,AMNRKRS,AMLFDNR,AMPOS FROM APFOLGE P"
                    + " LEFT JOIN KSTAGZUO k ON P.APFKSTKRS=KSZKSTKRS AND APFKST=KSZKST AND APFAGANG=KSZAGANG "
                    + " LEFT JOIN AMSTAMM M ON P.APFKSTKRS=AMKSTKRS AND APFKST=AMKST AND  APFMTYP=AMTYP AND AMSTDVAR='J' "
                    + " WHERE APFMANDANT=:APFMANDANT AND APFANRKRS=:APFANRKRS AND APFALFDNR=:APFALFDNR";
                    or1.Parameters.Add("APFMANDANT", OracleType.NVarChar).Value = dwData["APFMANDANT"].ToString();
                    or1.Parameters.Add("APFANRKRS", OracleType.NVarChar).Value = dwData["APFANRKRS"].ToString();
                    or1.Parameters.Add("APFALFDNR", OracleType.NVarChar).Value = dwData["APFALFDNR"].ToString();
                    break;
                case 5:
                    or1.CommandText = "SELECT P.*,M.AMNRKRS,AMLFDNR,AMPOS"
                    + ",KSZLIEFNR,KSZLO,KSZLB,KSZLP,KSZMANUELKZ FROM APFOLGE P LEFT JOIN KSTAGZUO K ON P.APFKSTKRS=KSZKSTKRS AND APFKST=KSZKST AND APFAGANG=KSZAGANG "
                    + " LEFT JOIN AMSTAMM M ON   APFKSTKRS=AMKSTKRS AND APFKST=AMKST AND APFMTYP=AMTYP AND AMSTDVAR='J'"
                    + "  WHERE APFMANDANT=:APFMANDANT AND APFANRKRS=:APFANRKRS AND APFALFDNR=:APFALFDNR"
                    + " ORDER BY P.APFMANDANT,P.APFANRKRS,P.APFALFDNR,P.APFAFOLGE";
                    or1.Parameters.Add("APFMANDANT", OracleType.NVarChar).Value = dwData["APFMANDANT"];
                    or1.Parameters.Add("APFANRKRS", OracleType.NVarChar).Value = dwData["APFANRKRS"];
                    or1.Parameters.Add("APFALFDNR", OracleType.NVarChar).Value = dwData["APFALFDNR"];
                    break;
                case 6: 
                    or1.CommandText = "SELECT DISTINCT APFMANDANT,APFANRKRS,APFALFDNR,APFAFOLGE,APFAGANG,APFKSTKRS,APFKST,APFMTYP,"
                    + "APFPARALKZ,APFBASMGE,APFME,APFRUECKKZ,APFKENNUNG,APFZUSATZ1,APFZUSATZ2,APFZUSATZ3,APFZUSATZ4,APFZUSATZ5,"
                    + "APFZUSTAND,APFFERTIG,APFMATABR,APFDOMPART,APFPRUEFANW,APFPROBE,APFANRKRS,APFALFDNR,APFAFOLGE,APFAEVOR,APFEINSTKA"
                    + ",KSZLIEFNR,KSZLO,KSZLB,KSZLP,KSZMANUELKZ,KSZLAGPLKZ,KSZAUFBKZ,AMNRKRS,AMLFDNR,AMPOS FROM APFOLGE P"
                    + " LEFT JOIN KSTAGZUO k ON P.APFKSTKRS=KSZKSTKRS AND APFKST=KSZKST AND APFAGANG=KSZAGANG "
                    + " LEFT JOIN AMSTAMM M ON P.APFKSTKRS=AMKSTKRS AND APFKST=AMKST AND  APFMTYP=AMTYP AND AMSTDVAR='J' "
                    + " ORDER BY P.APFMANDANT,P.APFANRKRS,P.APFALFDNR,P.APFAFOLGE"
                    ; 
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                tbLocal.TableName = "APFOLGE";
            }
            setReturn.Merge(Result);
            return setReturn;
        }
    }
}
